'use strict';

const Service = require('egg').Service;
const moment = require('moment');

class VehicleApplicationService extends Service {
  /**
   * 发起公车申请
   * @param {Object} params - 申请参数
   * @param {number} params.applicant_id - 申请人ID
   * @param {number} [params.department_id] - 申请人所在部门ID
   * @param {Date} params.apply_time - 申请时间
   * @param {Date} params.departure_date - 出发日期
   * @param {string} params.departure_time_period - 出发时间段（上午, 下午, 晚上）
   * @param {Date} params.return_date - 返回日期
   * @param {string} params.return_time_period - 返回时间段（上午, 下午, 晚上）
   * @param {string} params.destination - 目的地
   * @param {string} params.reason - 申请原因
   * @param {number[]} [params.passenger_ids] - 乘车人ID列表
   * @returns {Promise<Object>} 申请结果
   */
  async createApplication(params) {
    const {
      service
    } = this

    const {
      applicant_id,
      department_id,
      apply_time,
      departure_date,
      departure_time_period,
      return_date,
      return_time_period,
      destination,
      reason,
      passenger_ids = []
    } = params;

    // 插入公车申请单
    const applicationResult = await this.app.mysql.insert('ov_vehicle_application', {
      applicant_id,
      department_id,
      apply_time,
      departure_date,
      departure_time_period,
      return_date,
      return_time_period,
      destination,
      reason
    });

    const application_id = applicationResult.insertId;

    // 插入乘车人关联记录
    if (passenger_ids.length > 0) {
      const passengerRecords = passenger_ids.map(passenger_id => ({
        application_id,
        passenger_id
      }));
      await this.app.mysql.insert('ov_application_passenger', passengerRecords);
    }

    // // 发送短信
    // const smsResult = await service.sms.sendSms('17367073386', '测试短信');
    // // smsResult写入库中

    return {
      success: true,
      application_id
    };
  }

  /**
   * 获取申请列表
   * 支持按申请人、申请部门、用车人、日期、目的地筛选，支持分页查询
   * @param {Object} params - 查询参数
   * @param {number} [params.applicant_id] - 申请人ID
   * @param {number} [params.department_id] - 申请部门ID
   * @param {number} [params.passenger_id] - 用车人ID
   * @param {Date} [params.start_date] - 开始日期
   * @param {Date} [params.end_date] - 结束日期
   * @param {string} [params.destination] - 目的地，模糊匹配
   * @param {string} [params.status] - 状态
   * @param {number} [params.page=1] - 页码
   * @param {number} [params.page_size=10] - 每页条数
   * @returns {Promise<Object>} 包含列表和分页信息的对象
   */
  async getApplicationList(params) {
    const {
      applicant_id,
      department_id,
      passenger_id,
      start_date,
      end_date,
      destination,
      status,
      page = 1,
      page_size = 10
    } = params;

    // 构建 WHERE 条件
    let conditions = ['va.is_deleted = 0'];
    const queryParams = [];

    if (applicant_id) {
      conditions.push('va.applicant_id = ?');
      queryParams.push(applicant_id);
    }

    if (department_id) {
      conditions.push('va.department_id = ?');
      queryParams.push(department_id);
    }

    if (passenger_id) {
      conditions.push('ap.passenger_id = ?');
      queryParams.push(passenger_id);
    }

    if (start_date) {
      conditions.push('va.departure_date >= ?');
      queryParams.push(start_date);
    }

    if (end_date) {
      conditions.push('va.return_date <= ?');
      queryParams.push(end_date);
    }

    if (destination) {
      conditions.push('va.destination LIKE ?');
      queryParams.push(`%${destination}%`);
    }

    if (status) {
      conditions.push('va.status = ?');
      queryParams.push(`${status}`);
    }

    // 计算总数
    let countSql = `
      SELECT COUNT(DISTINCT va.application_id) as total 
      FROM ov_vehicle_application va
      LEFT JOIN ov_application_passenger ap ON va.application_id = ap.application_id
      WHERE ${conditions.join(' AND ')}
  `;
    const totalResult = await this.app.mysql.query(countSql, queryParams);
    const total = totalResult[0].total;

    // 查询数据
    const offset = (page - 1) * page_size;
    const sql = `
      SELECT va.*, p.person_name as applicant_name,p.phone_number as phone_number, d.department_name, 
             GROUP_CONCAT(pp.person_name) as passenger_names
      FROM ov_vehicle_application va
      LEFT JOIN tb_person p ON va.applicant_id = p.person_id AND p.is_deleted = 0
      LEFT JOIN tb_department d ON va.department_id = d.department_id AND d.is_deleted = 0
      LEFT JOIN ov_application_passenger ap ON va.application_id = ap.application_id
      LEFT JOIN tb_person pp ON ap.passenger_id = pp.person_id AND pp.is_deleted = 0
      WHERE ${conditions.join(' AND ')}
      GROUP BY va.application_id
      ORDER BY va.application_id DESC
      LIMIT ? OFFSET ?
  `;

    const result = await this.app.mysql.query(sql, [...queryParams, page_size, offset]);

    return {
      list: result,
      pagination: {
        total,
        page: parseInt(page),
        page_size: parseInt(page_size),
        total_pages: Math.ceil(total / page_size)
      }
    };
  }

  /**
   * 查询可用车辆信息
   * @returns {Promise<Array>} 可用车辆列表
   */
  async getAvailableVehicles() {
    const sql = `
        SELECT * 
        FROM ov_vehicle 
        WHERE is_deleted = 0
    `;
    return await this.app.mysql.query(sql);
  }

  /**
   * 查询可用驾驶员信息
   * @returns {Promise<Array>} 可用驾驶员列表
   */
  async getAvailableDrivers() {
    const sql = `
        SELECT od.*, tp.person_name 
        FROM ov_driver od
        JOIN tb_person tp ON od.person_id = tp.person_id
        WHERE od.is_deleted = 0 AND tp.is_deleted = 0
    `;
    return await this.app.mysql.query(sql);
  }

  /**
   * 根据申请单 ID 获取申请单的所有信息
   * @param {Object} params - 请求参数
   * @param {number} params.application_id - 申请单 ID
   * @returns {Promise<Object>} 申请单的详细信息
   */
  async getApplicationDetails(params) {
    const {
      application_id
    } = params;
    const result = {};

    // 获取申请单基础信息
    const baseInfoSql = `
        SELECT va.*, p.person_name as applicant_name,p.phone_number as phone_number, d.department_name 
        FROM ov_vehicle_application va
        LEFT JOIN tb_person p ON va.applicant_id = p.person_id AND p.is_deleted = 0
        LEFT JOIN tb_department d ON va.department_id = d.department_id AND d.is_deleted = 0
        WHERE va.application_id = ?
    `;
    const baseInfo = await this.app.mysql.query(baseInfoSql, [application_id]);
    result.baseInfo = baseInfo[0];

    // 获取乘车人信息
    const passengerSql = `
        SELECT p.person_name 
        FROM ov_application_passenger ap
        JOIN tb_person p ON ap.passenger_id = p.person_id AND p.is_deleted = 0
        WHERE ap.application_id = ?
    `;
    const passengers = await this.app.mysql.query(passengerSql, [application_id]);
    result.passengers = passengers.map(p => p.person_name);

    // 获取审核信息
    const approvalSql = `
        SELECT ova.*, p.person_name as approver_name 
        FROM ov_vehicle_approval ova
        JOIN tb_person p ON ova.approver_id = p.person_id AND p.is_deleted = 0
        WHERE ova.application_id = ?
    `;
    const approvals = await this.app.mysql.query(approvalSql, [application_id]);
    result.approvals = approvals;

    // 获取分配车辆和驾驶员信息
    const allocationSql = `
        SELECT ova.*, v.vehicle_number, d.person_name as driver_name 
        FROM ov_vehicle_allocation ova
        JOIN ov_vehicle v ON ova.vehicle_id = v.vehicle_id AND v.is_deleted = 0
        JOIN ov_driver od ON ova.driver_id = od.driver_id AND od.is_deleted = 0
        JOIN tb_person d ON od.person_id = d.person_id AND d.is_deleted = 0
        WHERE ova.application_id = ?
    `;
    const allocations = await this.app.mysql.query(allocationSql, [application_id]);
    result.allocations = allocations;

    // 获取取消信息
    if (result.baseInfo.status === '已取消') {
      const cancelerSql = `
            SELECT p.person_name 
            FROM tb_person p
            WHERE p.person_id = ?
        `;
      const canceler = await this.app.mysql.query(cancelerSql, [result.baseInfo.canceler_id]);
      result.cancelInfo = {
        cancellation_time: result.baseInfo.cancellation_time,
        cancel_reason: result.baseInfo.cancel_reason,
        canceler_name: canceler.length > 0 ? canceler[0].person_name : null
      };
    }

    return result;
  }

  /**
   * 取消用车申请
   * @param {Object} params - 请求参数
   * @param {number} params.application_id - 申请单ID
   * @param {number} params.canceler_id - 取消人ID
   * @param {number} params.cancel_reason - 取消人ID
   * @returns {Promise<Object>} 操作结果
   */
  async cancelApplication(params) {
    const {
      application_id,
      canceler_id,
      cancel_reason
    } = params;
    const result = await this.app.mysql.update('ov_vehicle_application', {
      status: '已取消',
      cancellation_time: new Date(),
      canceler_id,
      cancel_reason
    }, {
      where: {
        application_id,
        status: ['待审批', '已批准']
      }
    });
    return {
      success: result.affectedRows > 0
    };
  }

  /**
   * 审核用车申请
   * @param {Object} params - 请求参数
   * @param {number} params.application_id - 申请单ID
   * @param {number} params.approver_id - 审批人ID
   * @param {string} params.approval_result - 审批结果（'批准', '拒绝'）
   * @param {string} [params.comment] - 审批意见
   * @param {number} [params.vehicle_id] - 分配车辆ID（仅审批通过时需要）
   * @param {number} [params.driver_id] - 分配驾驶员ID（仅审批通过时需要）
   * @returns {Promise<Object>} 操作结果
   */
  async reviewApplication(params) {
    const {
      application_id,
      approver_id,
      approval_result,
      comment,
      vehicle_id,
      driver_id
    } = params;
    const status = approval_result === '批准' ? '已批准' : '已拒绝';
    const conn = await this.app.mysql.beginTransaction();
    try {
      // 更新申请单状态
      await conn.update('ov_vehicle_application', {
        status
      }, {
        where: {
          application_id,
          status: '待审批'
        }
      });
      // 插入审批记录
      await conn.insert('ov_vehicle_approval', {
        application_id,
        approver_id,
        approval_time: new Date(),
        approval_result,
        comment
      });

      if (approval_result === '批准') {
        // 审批通过，分配车辆和驾驶员
        await conn.insert('ov_vehicle_allocation', {
          application_id,
          vehicle_id,
          driver_id
        });
      }
      await conn.commit();
      return {
        success: true
      };
    } catch (err) {
      await conn.rollback();
      this.ctx.logger.error(err);
      return {
        success: false,
        error: err.message
      };
    }
  }

  // 根据车辆 ID 获取分配给该车辆的所有申请单信息
  async getApplicationsByVehicleId(params) {
    const {
      vehicle_id,
      status
    } = params;
    let conditions = ['ova.vehicle_id = ?'];
    const queryParams = [vehicle_id];

    if (status) {
      conditions.push('va.status = ?');
      queryParams.push(status);
    }

    const sql = `
      SELECT va.*, p.person_name as applicant_name, d.department_name 
      FROM ov_vehicle_allocation ova
      JOIN ov_vehicle_application va ON ova.application_id = va.application_id
      LEFT JOIN tb_person p ON va.applicant_id = p.person_id AND p.is_deleted = 0
      LEFT JOIN tb_department d ON va.department_id = d.department_id AND d.is_deleted = 0
      WHERE ${conditions.join(' AND ')}
  `;
    return await this.app.mysql.query(sql, queryParams);
  }

  // 根据驾驶员 ID 获取分配给该驾驶员的所有申请单信息
  async getApplicationsByDriverId(params) {
    const {
      driver_id,
      status
    } = params;
    let conditions = ['ova.driver_id = ?'];
    const queryParams = [driver_id];

    if (status) {
      conditions.push('va.status = ?');
      queryParams.push(status);
    }

    const sql = `
      SELECT va.*, p.person_name as applicant_name, d.department_name, v.vehicle_number 
      FROM ov_vehicle_allocation ova
      JOIN ov_vehicle_application va ON ova.application_id = va.application_id
      LEFT JOIN tb_person p ON va.applicant_id = p.person_id AND p.is_deleted = 0
      LEFT JOIN tb_department d ON va.department_id = d.department_id AND d.is_deleted = 0
      LEFT JOIN ov_vehicle v ON ova.vehicle_id = v.vehicle_id AND v.is_deleted = 0
      WHERE ${conditions.join(' AND ')}
  `;
    return await this.app.mysql.query(sql, queryParams);
  }

  async getVehicleDailyRecords({
    year_month
  }) {
    const ctx = this.ctx;
    // 参数校验
    if (year_month && !moment(year_month, 'YYYY-MM', true).isValid()) {
      ctx.throw(500, 'Invalid year_month format. It should be in YYYY-MM format.');
    }
    let sql = `
        SELECT
            v.vehicle_id,
            v.vehicle_number,
            v.vehicle_type,
            v.brand_model,
            v.frame_number,
            v.registration_date,
            v.power_type,
            v.displacement,
            dr.record_date,
            dr.record_id,
            dr.recorder_id,
            p.person_name,
            dr.start_km,
            dr.end_km,
            dr.fuel_cost,
            dr.charge_cost,
            dr.repair_cost,
            dr.roads_cost,
            dr.other_cost,
            dr.remark
        FROM
            ov_vehicle v
            LEFT JOIN ov_driver_vehicle_daily_record dr ON v.vehicle_id = dr.vehicle_id 
            AND dr.record_date BETWEEN ? 
            AND ? 
            LEFT JOIN tb_person p ON dr.recorder_id = p.person_id
        WHERE
            v.is_deleted = 0 -- 筛选未被删除的车辆
        ORDER BY
            v.vehicle_id, dr.record_date;
    `;
    const params = [];

    if (year_month) {
      const year = year_month.slice(0, 4);
      const month = year_month.slice(5);
      const startDate = `${year}-${month}-01`;
      const endDate = `${year}-${month}-${new Date(year, month, 0).getDate()}`;
      params.push(startDate, endDate);
    }
    try {
      const result = await this.app.mysql.query(sql, params);
      return this.formatResult(result, year_month);
    } catch (error) {
      // 记录错误日志，这里简单打印，实际可集成日志系统
      console.error('Error in getVehicleDailyRecords:', error);
      ctx.throw(500, error);
    }
  }

  formatResult(result, year_month) {
    const start = moment(year_month, 'YYYY-MM');
    const end = start.clone().endOf('month');
    const daysInMonth = [];
    let current = start;
    while (current.isSameOrBefore(end)) {
      daysInMonth.push(current.format('YYYY-MM-DD'));
      current = current.add(1, 'day');
    }

    const vehicleMap = new Map();
    result.forEach(item => {
      const vehicleId = item.vehicle_id;
      if (!vehicleMap.has(vehicleId)) {
        const vehicle = {
          vehicle_id: vehicleId,
          vehicle_number: item.vehicle_number,
          vehicle_type: item.vehicle_type,
          brand_model: item.brand_model,
          frame_number: item.frame_number,
          registration_date: item.registration_date,
          power_type: item.power_type,
          displacement: item.displacement,
        };
        daysInMonth.forEach(day => {
          vehicle[day] = '{}';
        });
        vehicleMap.set(vehicleId, vehicle);
      }

      const vehicle = vehicleMap.get(vehicleId);
      if (item.record_date) {
        const formattedDate = moment(item.record_date).format('YYYY-MM-DD');
        const record = {
          record_id: item.record_id,
          recorder_id: item.recorder_id,
          recorder_name: item.person_name,
          start_km: item.start_km,
          end_km: item.end_km,
          fuel_cost: item.fuel_cost,
          charge_cost: item.charge_cost,
          repair_cost: item.repair_cost,
          roads_cost: item.roads_cost,
          other_cost: item.other_cost,
          remark: item.remark
        };
        vehicle[formattedDate] = JSON.stringify(record);
      }
    });

    return Array.from(vehicleMap.values());
  }

  /**
   * 新增车辆每日记录
   * @param {Object} params - 请求参数
   * @param {number} params.vehicle_id - 车辆 ID
   * @param {number} params.recorder_id - 记录人 ID
   * @param {string} params.record_date - 记录日期，格式为 YYYY-MM-DD
   * @param {number} [params.start_km] - 上班时公里数
   * @param {number} [params.end_km] - 下班时公里数
   * @param {number} [params.fuel_cost] - 燃油费
   * @param {number} [params.charge_cost] - 充电费
   * @param {number} [params.repair_cost] - 维修保养费
   * @param {number} [params.roads_cost] - 过路过桥费
   * @param {number} [params.other_cost] - 其他费用
   * @param {string} [params.remark] - 备注信息
   * @returns {Promise<Object>} 操作结果
   */
  async createVehicleDailyRecord(params) {
    const {
      vehicle_id,
      recorder_id,
      record_date,
      start_km,
      end_km,
      fuel_cost,
      charge_cost,
      repair_cost,
      roads_cost,
      other_cost,
      remark
    } = params;

    // 参数类型校验
    if (typeof vehicle_id !== 'number' || typeof recorder_id !== 'number') {
      this.ctx.throw(400, 'vehicle_id 和 recorder_id 必须为数字类型');
    }
    if (typeof record_date !== 'string' || !/^\d{4}-\d{2}-\d{2}$/.test(record_date)) {
      this.ctx.throw(400, 'record_date 必须为 YYYY-MM-DD 格式的字符串');
    }
    const optionalNumberFields = ['start_km', 'end_km', 'fuel_cost', 'charge_cost', 'repair_cost', 'roads_cost', 'other_cost'];
    optionalNumberFields.forEach(field => {
      if (params[field] !== undefined && typeof params[field] !== 'number') {
        this.ctx.throw(400, `${field} 必须为数字类型`);
      }
    });

    try {
      const result = await this.app.mysql.query('INSERT INTO ov_driver_vehicle_daily_record (vehicle_id, recorder_id, record_date, start_km, end_km, fuel_cost, charge_cost, repair_cost, roads_cost, other_cost, remark) VALUES (?,?,?,?,?,?,?,?,?,?,?)',
        [vehicle_id, recorder_id, record_date, start_km, end_km, fuel_cost, charge_cost, repair_cost, roads_cost, other_cost, remark]);
      return result;
    } catch (error) {
      this.ctx.throw(500, error);
    }
  }

  /**
   * 编辑车辆每日记录
   * @param {Object} params - 请求参数
   * @param {number} params.record_id - 记录 ID
   * @param {number} [params.vehicle_id] - 车辆 ID
   * @param {number} [params.recorder_id] - 记录人 ID
   * @param {string} [params.record_date] - 记录日期，格式为 YYYY-MM-DD
   * @param {number} [params.start_km] - 上班时公里数
   * @param {number} [params.end_km] - 下班时公里数
   * @param {number} [params.fuel_cost] - 燃油费
   * @param {number} [params.charge_cost] - 充电费
   * @param {number} [params.repair_cost] - 维修保养费
   * @param {number} [params.roads_cost] - 过路过桥费
   * @param {number} [params.other_cost] - 其他费用
   * @param {string} [params.remark] - 备注信息
   * @returns {Promise<Object>} 操作结果
   */
  async updateVehicleDailyRecord(params) {
    const {
      record_id
    } = params;

    // 参数类型校验
    if (typeof record_id !== 'number') {
      this.ctx.throw(400, 'record_id 必须为数字类型');
    }
    const optionalNumberFields = ['vehicle_id', 'recorder_id', 'start_km', 'end_km', 'fuel_cost', 'charge_cost', 'repair_cost', 'roads_cost', 'other_cost'];
    optionalNumberFields.forEach(field => {
      if (params[field] !== undefined && typeof params[field] !== 'number') {
        this.ctx.throw(400, `${field} 必须为数字类型`);
      }
    });
    if (params.record_date !== undefined && (typeof params.record_date !== 'string' || !/^\d{4}-\d{2}-\d{2}$/.test(params.record_date))) {
      this.ctx.throw(400, 'record_date 必须为 YYYY-MM-DD 格式的字符串');
    }

    const setFields = [];
    const values = [];
    const fields = ['vehicle_id', 'recorder_id', 'record_date', 'start_km', 'end_km', 'fuel_cost', 'charge_cost', 'repair_cost', 'roads_cost', 'other_cost', 'remark'];

    fields.forEach(field => {
      if (params[field] !== undefined) {
        setFields.push(`${field} =?`);
        values.push(params[field]);
      }
    });

    values.push(record_id);

    const setClause = setFields.join(', ');
    try {
      const result = await this.app.mysql.query(`UPDATE ov_driver_vehicle_daily_record SET ${setClause} WHERE record_id =?`, values);
      return result;
    } catch (error) {
      this.ctx.throw(500, error);
    }
  }

  /**
   * 删除车辆每日记录
   * @param {Object} params - 请求参数
   * @param {number} params.record_id - 记录 ID
   * @returns {Promise<Object>} 操作结果
   */
  async deleteVehicleDailyRecord(params) {
    const {
      record_id
    } = params;

    // 参数类型校验
    if (typeof record_id !== 'number') {
      this.ctx.throw(400, 'record_id 必须为数字类型');
    }

    try {
      const result = await this.app.mysql.query(`DELETE FROM ov_driver_vehicle_daily_record WHERE record_id = ${record_id};`)
      console.log(result)
      return result;
    } catch (error) {
      this.ctx.throw(500, error);
    }
  }
}

module.exports = VehicleApplicationService;